﻿using Aspose.Cells;
using BarcodeDelay.bean;
using BarcodeDelay.business;
using BarcodeDelay.FrameDecoder;
using JTParcrelHelper.bean;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace BarcodeDelay.utility
{
    public class UtilityExcel
    {
        private static UtilityExcel instance;
        public static UtilityExcel Instance
        {
            get
            {
                if (instance == null)
                {
                    instance = new UtilityExcel();
                }
                return instance;
            }
        }

        /// <summary>
        /// 获取指定编号表单
        /// </summary>
        /// <param name="workbook">已打开工作簿对象</param>
        /// <param name="sheetIndex">表单索引</param>
        /// <returns></returns>
        public Worksheet GetWorksheet(Workbook workbook, int sheetIndex)
        {
            return sheetIndex < 0 ? null : workbook.Worksheets[sheetIndex];
        }

        /// <summary>
        /// 根据路径获取指定编号表单
        /// </summary>
        /// <param name="path">工作簿路径</param>
        /// <param name="sheetIndex">表单索引</param>
        /// <returns></returns>
        public Worksheet GetWorksheet(string path, int sheetIndex)
        {
            return GetWorksheet(OpenWorkbook(path), sheetIndex);
        }

        /// <summary>
        /// 打开指定路径工作簿
        /// </summary>
        /// <param name="path">工作簿路径</param>
        /// <returns></returns>
        public Workbook OpenWorkbook(string path)
        {
            return File.Exists(path) ? new Workbook(path) : null;
        }




        /// <summary>
        /// 读取Excel中数据
        /// </summary>
        /// <param name="strExcelPath"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public ArrayList GetWechatDirExcelData(string strExcelPath)
        {
            ArrayList arListAllData = new ArrayList();
            try
            {
                Hashtable htRiverStaIndexMap = new Hashtable();
                Worksheet ws = GetWorksheet(strExcelPath, 0);
                Cells cells = ws.Cells;
                string strPriorRiverAndSta = "";
                ArrayList arListTmp = new ArrayList();
                bool isNextLineIsData = false;
                for (int i = 0; i < cells.MaxDataRow + 1; i++)
                {
                    if(cells.MaxColumn==0)
                    {
                        string barcode = cells[i, 0].StringValue.Trim();
                        if(barcode.StartsWith("JT"))
                        {
                            arListAllData.Add(barcode.Trim().Replace(" ",""));
                        }
                    }
                    else if(cells.MaxColumn >= 1)
                    {
                        string barcode = cells[i, 0].StringValue.Trim();
                        if (!barcode.StartsWith("JT"))
                        {
                            continue;
                        }
                        string weight = "";
                        for(int column=1; column <= cells.MaxColumn; column++)
                        {
                            string strWeight = cells[i, column].StringValue.Trim();
                            if(MathUtility.isRightDigital(strWeight))
                            {
                                decimal dweight = Convert.ToDecimal(strWeight);
                                if(dweight<15)
                                {
                                    weight = strWeight;
                                }
                                
                            }
                        }
                        arListAllData.Add(barcode.Trim().Replace(" ", "") + " " + weight.Replace(" ", "").Trim());

                    }


                }



            }

            catch (Exception ex)
            {
                logHelp.LogHelper.Instance.writeLog(strExcelPath + " 解析出错:" + ex.ToString());
            }
            return arListAllData;
        }




        /// <summary>
        /// 读取Excel中数据
        /// </summary>
        /// <param name="strExcelPath"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public void GetExcelData(string strExcelPath)
        {
            ArrayList arListAllData = new ArrayList();
            string strCity = "";
            string strRegSite = StoParams.Instance.register_site;
      
            if (MemberInfo.Instance.htSiteCityMap.ContainsKey(strRegSite))
            {
                strCity = MemberInfo.Instance.htSiteCityMap[strRegSite] as string;
            }
            try
            {
                Hashtable htRiverStaIndexMap = new Hashtable();
                Worksheet ws = GetWorksheet(strExcelPath, 0);
                Cells cells = ws.Cells;
                string strPriorRiverAndSta = "";
                ArrayList arListTmp = new ArrayList();
                bool isNextLineIsData = false;
                for (int i = 0; i < cells.MaxDataRow + 1; i++)
                {
                   if(utility.Utility.IsNumeric(cells[i,1].StringValue.Trim()))
                   {
                        string sourceCity= cells[i, 3].StringValue.Trim();
                        if(sourceCity!=strCity&& strCity.Length>1)
                        {
                            continue;
                        }
                        string desCity = cells[i, 5].StringValue.Trim();
                        string effiDay = cells[i, 11].StringValue.Trim();
                        if(utility.Utility.IsNumeric(effiDay))
                        {
                            MemberInfo.Instance.htDesCityEffi[desCity] = Convert.ToInt32(effiDay)+1;
                        }
                    }          

                }               
         

            }

            catch (Exception ex)
            {
            }
        }


        /// <summary>
        /// 导出Excel表格
        /// </summary>
        /// <param name="list">数据集合</param>
        /// <param name="header">数据表头</param>
        /// <returns></returns>
        public bool ExportExcel(DataTable dt, string[] header,string path)
        {
            bool res = false;
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            try
            {
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                if (dt.Rows.Count <= 0)
                {
                    MessageBox.Show("数据行为空");                    
                    return res;
                }
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 12;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中

                int rowIndex = 0;
                for (int i = 0; i < header.Length; i++)
                {
                    sheet.Cells[rowIndex, i].PutValue(header[i]);
                    sheet.Cells[rowIndex, i].SetStyle(style);
                    sheet.Cells.SetColumnWidth(i, 25);//设置宽度
                    if (i==9|| i == 10)
                    {
                        sheet.Cells.SetColumnWidth(i, 15);//设置宽度
                    }
                }
                for (int i = 0; i < dt.Rows.Count; i++)//遍历DataTable行
                {
                    sheet.Cells[i + 1, 0].PutValue(dt.Rows[i]["揽收网点"].ToString());
                    sheet.Cells[i + 1, 1].PutValue(dt.Rows[i]["运单号"].ToString());
                    sheet.Cells[i + 1, 2].PutValue(dt.Rows[i]["当前站点"].ToString());
                    sheet.Cells[i + 1, 3].PutValue(dt.Rows[i]["延误原因"].ToString());
                    sheet.Cells[i + 1, 4].PutValue(dt.Rows[i]["类型"].ToString());
                    sheet.Cells[i + 1, 5].PutValue(dt.Rows[i]["主线任务号"].ToString());
                    sheet.Cells[i + 1, 6].PutValue(dt.Rows[i]["计划到件时间"].ToString());

                    sheet.Cells[i + 1,7].PutValue(dt.Rows[i]["计划发出时间"].ToString());
                    sheet.Cells[i + 1, 8].PutValue(dt.Rows[i]["实际发出时间"].ToString());

                    sheet.Cells[i + 1, 9].PutValue(dt.Rows[i]["班车延误"].ToString());

                    sheet.Cells[i + 1, 10].PutValue(dt.Rows[i]["既囤又发"].ToString());
                    sheet.Cells[i + 1, 11].PutValue(dt.Rows[i]["操作时长"].ToString());


                    sheet.Cells[i + 1, 12].PutValue(dt.Rows[i]["异常报备信息"].ToString());
                    sheet.Cells[i + 1, 13].PutValue(dt.Rows[i]["揽收时间"].ToString());
                    sheet.Cells[i + 1, 14].PutValue(dt.Rows[i]["入库时间"].ToString());
                    sheet.Cells[i + 1, 15].PutValue(dt.Rows[i]["签收时间"].ToString());
                   

                    sheet.Cells[i + 1, 16].PutValue(dt.Rows[i]["签收网点"].ToString());
                    sheet.Cells[i + 1, 17].PutValue(dt.Rows[i]["最后扫描时间"].ToString());
                    sheet.Cells[i + 1, 18].PutValue(dt.Rows[i]["最后扫描类型"].ToString());

                    sheet.Cells[i + 1, 19].PutValue(dt.Rows[i]["延误天数"].ToString());

                }
            }
            catch (Exception e)
            {
                MessageBox.Show("导出数据异常"+e.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(path);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }


        /// <summary>
        /// 导出Excel表格
        /// </summary>
        /// <param name="list">数据集合</param>
        /// <param name="header">数据表头</param>
        /// <returns></returns>
        public bool GenExcelForStatic(ArrayList arListHandleBarcodes, string[] header, string path)
        {
            bool res = false;
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            try
            {
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                if (arListHandleBarcodes.Count <= 0)
                {
                   // MessageBox.Show("数据行为空");
                    return res;
                }
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中

                int rowIndex = 0;
                for (int i = 0; i < header.Length; i=i+1)
                {
                    sheet.Cells[rowIndex, i].PutValue(header[i]);
                    sheet.Cells[rowIndex, i].SetStyle(style);
                    sheet.Cells.SetColumnWidth(i, 25);//设置宽度

                }
                //foreach(string strTaskType in header)
                for(int i=0;i < header.Length;i=i+3)
                {
                    string strTaskType = header[i].ToString();
                    int curRowIndex = 1;
                    foreach (string strLine in arListHandleBarcodes)
                    {
                        if(strLine.Contains(strTaskType))
                        {
                            string[] arr = strLine.Split('$');
                            if(arr[0]!= strTaskType)
                            {
                                continue;
                            }
                            string lanShouTime = arr[arr.Length - 1];
                            if(!lanShouTime.Contains("-"))
                            {
                                lanShouTime = "";
                            }
                            sheet.Cells[curRowIndex, i].PutValue(arr[1].ToString());
                            sheet.Cells[curRowIndex, i + 1].PutValue(lanShouTime);
                            sheet.Cells[curRowIndex, i+2].PutValue(arr[2].ToString());
                            curRowIndex++;
                        }
                    }
                }
            }
            catch (Exception e)
            {
                logHelp.LogHelper.Instance.writeLog("生成excel数据异常" + e.ToString());
                //MessageBox.Show("导出数据异常" + e.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(path);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }





        public bool GenExcelForStatic(ArrayList arListStatics, string sPath)
        {
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            int curR = 0;
            try
            {

               
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果"; 
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中

                int rowIndex = 0;
                sheet.Cells[rowIndex, 0].PutValue("单号");
                sheet.Cells[rowIndex, 0].SetStyle(style);
                sheet.Cells.SetColumnWidth(0, 18);//设置宽度

                sheet.Cells[rowIndex, 1].PutValue("类型");
                sheet.Cells[rowIndex, 1].SetStyle(style);
                sheet.Cells.SetColumnWidth(1, 15);//设置宽度

                sheet.Cells[rowIndex, 2].PutValue("揽收时间");
                sheet.Cells[rowIndex, 2].SetStyle(style);
                sheet.Cells.SetColumnWidth(2, 15);//设置宽度

                sheet.Cells[rowIndex, 3].PutValue("查件人");
                sheet.Cells[rowIndex, 3].SetStyle(style);
                sheet.Cells.SetColumnWidth(3, 30);//设置宽度

                sheet.Cells[rowIndex, 4].PutValue("查件时间");
                sheet.Cells[rowIndex, 4].SetStyle(style);
                sheet.Cells.SetColumnWidth(4, 18);//设置宽度

                sheet.Cells[rowIndex, 5].PutValue("客户名称");
                sheet.Cells[rowIndex, 5].SetStyle(style);
                sheet.Cells.SetColumnWidth(5, 45);//设置宽度

                sheet.Cells[rowIndex, 6].PutValue("查件群");
                sheet.Cells[rowIndex, 6].SetStyle(style);
                sheet.Cells.SetColumnWidth(6, 30);//设置宽度

                sheet.Cells[rowIndex, 7].PutValue("揽收网点");
                sheet.Cells[rowIndex, 7].SetStyle(style);
                sheet.Cells.SetColumnWidth(7, 20);//设置宽度

                sheet.Cells[rowIndex, 8].PutValue("任务描述");
                sheet.Cells[rowIndex, 8].SetStyle(style);
                sheet.Cells.SetColumnWidth(8, 40);//设置宽度



                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1; ;
                foreach (string strLine in arListStatics)
                {
                    curIndex++;
                    curR++;

                    if (strLine.Length>12&& strLine.Contains("$"))
                    {
                        string[] arr = strLine.Split('$');

               

                        string addtionStr = "";
                        /*if(curIndex< arListStatics.Count-1)
                        {
                            string nextLine = arr[curIndex + 1];
                            string[] arr2 = nextLine.Split('$');
                            if(arr2.Length==1)
                            {
                                addtionStr = nextLine;
                            }

                        }*/
                        if(arr.Length<4)
                        {
                            continue;
                        }

                        string lanShouTime = arr[3];
                        if (!lanShouTime.Contains("-"))
                        {
                            lanShouTime = "";
                        }

                        string queryTime ="";
                        if(arr.Length>=8)
                        {
                            queryTime = arr[7];
                        }
                        if (!queryTime.Contains("-"))
                        {
                            queryTime = "";
                        }
                        if(!StoParams.Instance.register_site.Contains("凤岗"))
                        {
                            if (queryTime.Length > 5 && queryTime.Contains(" "))
                            {
                                //queryTime = queryTime.Substring(0, queryTime.IndexOf(' '));
                            }
                        }
                        

                        sheet.Cells[curRowIndex, 0].PutValue(arr[1].ToString());
                        sheet.Cells[curRowIndex, 1].PutValue(arr[0].ToString());
                        sheet.Cells[curRowIndex, 2].PutValue(lanShouTime);
                        if(arr.Length>=5)
                        {
                            sheet.Cells[curRowIndex, 3].PutValue(arr[2].ToString());

                            sheet.Cells[curRowIndex, 4].PutValue(queryTime);
                        }

                        if (arr.Length > 8)
                        {
                            sheet.Cells[curRowIndex, 5].PutValue(arr[8].ToString());
                            string roomid = arr[5].ToString();
                            if(FrameDecoderManager.Instance.htGroupNameRooms.ContainsKey(roomid))
                            {
                                string groupName = FrameDecoderManager.Instance.htGroupNameRooms[roomid].ToString();
                                sheet.Cells[curRowIndex, 6].PutValue(groupName);
                            }
                        }
                        if (arr.Length > 9)
                        {

                            sheet.Cells[curRowIndex, 7].PutValue(arr[9].ToString());
                        }

                        sheet.Cells[curRowIndex, 8].PutValue(arr[4].ToString());

                        curRowIndex++;
                    }
                }
            }
            catch(Exception e)
            {
               
                logHelp.LogHelper.Instance.writeLog("GenExcelForStatic 生成excel数据异常 curR="+ curR.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }

        public bool GenExcelForUserPhone(ArrayList arListStatics, string sPath, out string outPutFileUrl)
        {
            outPutFileUrl = "";
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            try
            {
                sPath = sPath + "\\" + "客户信息汇总_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                outPutFileUrl = sPath;
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style.IsTextWrapped = true;

                int rowIndex = 0;
                int culumnIndex = 0;
                sheet.Cells[rowIndex, culumnIndex].PutValue("单号信息");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 60);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("登录用户");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度





                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1;

                Aspose.Cells.Style styleMsg = wb.CreateStyle();
                styleMsg.Font.Size = 11;//文字大小
                styleMsg.IsTextWrapped = true;

                foreach (CustomerBean customerBean in arListStatics)
                {
                    curIndex++;

                    

                    int colum = 0;
                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.waybillNo+"----"+ customerBean.receiverMobilePhone);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.tokenUser);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    curRowIndex++;
                }
            }
            catch (Exception e)
            {
                logHelp.LogHelper.Instance.writeLog("GenExcelForStatic 生成excel数据异常" + e.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }

        public bool GenExcelForStaticMsgBoardYonghe(ArrayList arListStatics, string sPath, out string outPutFileUrl)
        {
            outPutFileUrl = "";
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            try
            {
                sPath = sPath + "\\" + "进港留言汇总_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                outPutFileUrl = sPath;
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style.IsTextWrapped = true;

                int rowIndex = 0;
                int culumnIndex = 0;

                sheet.Cells[rowIndex, culumnIndex].PutValue("单号");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度

                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("派件员");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("驿站记录");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("三段码");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 16);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("本网点最新扫描记录");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 16);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("最新物流记录");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 55);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言次数");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 5);//设置宽度


                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("单号信息");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 40);//设置宽度


                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板1");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板2");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板3");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板4");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板5");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度



                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1;

                Aspose.Cells.Style styleMsg = wb.CreateStyle();
                styleMsg.Font.Size = 11;//文字大小
                styleMsg.IsTextWrapped = true;

                foreach (CustomerBean customerBean in arListStatics)
                {
                    curIndex++;


                    string customerInfo = UtilityHelp.Instance.getCustomerInfo(customerBean);
                    UtilityHelp.Instance.addCustomerInfoReply(customerBean);

                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < customerBean.arListReplyContent.Count; i++)
                    {
                        sb.AppendLine(customerBean.arListReplyContent[i].ToString());
                    }

                    StringBuilder sbRoadMap = new StringBuilder();
                    for (int i = 0; i < customerBean.arListLastRoadMap.Count; i++)
                    {
                        sbRoadMap.AppendLine(customerBean.arListLastRoadMap[i].ToString());
                    }

                    int colum = 0;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.waybillNo);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.dispatchName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.warehouseName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.terminalDispatchCode);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.waybillStatus);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;



                    sheet.Cells[curRowIndex, colum].PutValue(sbRoadMap);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.leaveMsgCout);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerInfo);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[0]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[1]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[2]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[3]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[4]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    /*if (arr.Length >= 5)
                    {
                        sheet.Cells[curRowIndex, 3].PutValue(arr[2].ToString());

                        sheet.Cells[curRowIndex, 4].PutValue(queryTime);
                    }

                    if (arr.Length > 8)
                    {
                        sheet.Cells[curRowIndex, 5].PutValue(arr[8].ToString());
                    }*/

                    curRowIndex++;
                }
            }
            catch (Exception e)
            {
                logHelp.LogHelper.Instance.writeLog("GenExcelForStatic 生成excel数据异常" + e.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }



        public bool GenExcelForStaticMsgBoardY(ArrayList arListStatics, string sPath, out string outPutFileUrl)
        {
            outPutFileUrl = "";
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            try
            {
                sPath = sPath + "\\" + "进港留言汇总_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                outPutFileUrl = sPath;
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style.IsTextWrapped = true;

                int rowIndex = 0;
                int culumnIndex = 0;

                sheet.Cells[rowIndex, culumnIndex].PutValue("单号");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度

                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("客户名称");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度

                culumnIndex++;


                sheet.Cells[rowIndex, culumnIndex].PutValue("派件员");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("驿站记录");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("三段码");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 16);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("本网点最新扫描记录");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 16);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("最新物流记录");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 55);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言次数");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 5);//设置宽度


                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("单号信息");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 40);//设置宽度


                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板1");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板2");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板3");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板4");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板5");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度



                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1;

                Aspose.Cells.Style styleMsg = wb.CreateStyle();
                styleMsg.Font.Size = 11;//文字大小
                styleMsg.IsTextWrapped = true;

                foreach (CustomerBean customerBean in arListStatics)
                {
                    curIndex++;


                    string customerInfo = UtilityHelp.Instance.getCustomerInfo(customerBean);
                    UtilityHelp.Instance.addCustomerInfoReply(customerBean);

                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < customerBean.arListReplyContent.Count; i++)
                    {
                        sb.AppendLine(customerBean.arListReplyContent[i].ToString());
                    }

                    StringBuilder sbRoadMap = new StringBuilder();
                    for (int i = 0; i < customerBean.arListLastRoadMap.Count; i++)
                    {
                        sbRoadMap.AppendLine(customerBean.arListLastRoadMap[i].ToString());
                    }

                    int colum = 0;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.waybillNo);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.customerName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.dispatchName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.warehouseName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.terminalDispatchCode);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.waybillStatus);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;



                    sheet.Cells[curRowIndex, colum].PutValue(sbRoadMap);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.leaveMsgCout);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerInfo);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[0]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[1]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[2]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[3]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[4]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    /*if (arr.Length >= 5)
                    {
                        sheet.Cells[curRowIndex, 3].PutValue(arr[2].ToString());

                        sheet.Cells[curRowIndex, 4].PutValue(queryTime);
                    }

                    if (arr.Length > 8)
                    {
                        sheet.Cells[curRowIndex, 5].PutValue(arr[8].ToString());
                    }*/

                    curRowIndex++;
                }
            }
            catch (Exception e)
            {
                logHelp.LogHelper.Instance.writeLog("GenExcelForStatic 生成excel数据异常" + e.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }


        public bool GenExcelForStaticMsgBoard(ArrayList arListStatics, string sPath, out string outPutFileUrl)
        {
            outPutFileUrl = "";
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            try
            {
                sPath = sPath + "\\" + "进港留言汇总_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                outPutFileUrl = sPath;
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style.IsTextWrapped = true;

                int rowIndex = 0;
                int culumnIndex = 0;
                sheet.Cells[rowIndex, culumnIndex].PutValue("派件员");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("驿站记录");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("三段码");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 16);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("最新物流记录");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 55);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言次数");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 5);//设置宽度


                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("单号信息");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 40);//设置宽度


                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板1");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板2");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板3");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 20);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板4");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度
                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("留言板5");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度



                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1;

                Aspose.Cells.Style styleMsg = wb.CreateStyle();
                styleMsg.Font.Size = 11;//文字大小
                styleMsg.IsTextWrapped = true;

                foreach (CustomerBean customerBean in arListStatics)
                {
                    curIndex++;


                    string customerInfo = UtilityHelp.Instance.getCustomerInfo(customerBean);
                    UtilityHelp.Instance.addCustomerInfoReply(customerBean);

                    StringBuilder sb = new StringBuilder();
                    for (int i = 0; i < customerBean.arListReplyContent.Count; i++)
                    {
                        sb.AppendLine(customerBean.arListReplyContent[i].ToString());
                    }

                    StringBuilder sbRoadMap = new StringBuilder();
                    for (int i = 0; i < customerBean.arListLastRoadMap.Count; i++)
                    {
                        sbRoadMap.AppendLine(customerBean.arListLastRoadMap[i].ToString());
                    }

                    int colum = 0;
                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.dispatchName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.warehouseName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.terminalDispatchCode);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(sbRoadMap);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.leaveMsgCout);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerInfo);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[0]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[1]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[2]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[3]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(customerBean.arListReplyContent[4]);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    /*if (arr.Length >= 5)
                    {
                        sheet.Cells[curRowIndex, 3].PutValue(arr[2].ToString());

                        sheet.Cells[curRowIndex, 4].PutValue(queryTime);
                    }

                    if (arr.Length > 8)
                    {
                        sheet.Cells[curRowIndex, 5].PutValue(arr[8].ToString());
                    }*/

                    curRowIndex++;
                }
            }
            catch (Exception e)
            {
                logHelp.LogHelper.Instance.writeLog("GenExcelForStatic 生成excel数据异常" + e.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }

        public bool GenExcelForProblemReturn(ArrayList arListStatics, string sPath,out string outPutFileUrl)
        {
            outPutFileUrl = "";
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            try
            {
                sPath= sPath+ "\\" + "出港退转问题件汇总_"  + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                outPutFileUrl = sPath;
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style.IsTextWrapped = true;

                int rowIndex = 0;
                int culumnIndex = 0;
                sheet.Cells[rowIndex, culumnIndex].PutValue("单号");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 25);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("退回/转寄原因 ");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 30);//设置宽度

                culumnIndex++;
                sheet.Cells[rowIndex, culumnIndex].PutValue("客户名称");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 30);//设置宽度              


                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1;

                Aspose.Cells.Style styleMsg = wb.CreateStyle();
                styleMsg.Font.Size = 11;//文字大小
                styleMsg.IsTextWrapped = true;

                foreach (ParcelBean parcelBean in arListStatics)
                {
                    curIndex++;                   

                    int colum = 0;
                    sheet.Cells[curRowIndex, colum].PutValue(parcelBean.wayBill);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    sheet.Cells[curRowIndex, colum].PutValue(parcelBean.rebackTransferReason);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(parcelBean.customerName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                   
                    curRowIndex++;
                }
            }
            catch (Exception e)
            {
                logHelp.LogHelper.Instance.writeLog("GenExcelForProblemReturn 生成excel数据异常" + e.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }




        public bool GenExcelForOutBoundAck(ArrayList arListStatics, string sPath, out string outPutFileUrl)
        {
            outPutFileUrl = "";
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            try
            {
                sPath = sPath + "\\" + "出港自动回复汇总_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                outPutFileUrl = sPath;
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style.IsTextWrapped = true;

                int rowIndex = 0;
                int culumnIndex = 0;
                sheet.Cells[rowIndex, culumnIndex].PutValue("单号");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 25);//设置宽度

                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("寄件网点");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 25);//设置宽度

                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("问题件一级类型");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 25);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("问题件二级类型");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 40);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("问题件描述");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 40);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("操作类型");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("登记网点");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 30);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("登记时间");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("客户编码");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("客户名称");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 40);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("反馈人员");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("回复是否成功");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("回复内容");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 60);//设置宽度
                culumnIndex++;
  


                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1;

                Aspose.Cells.Style styleMsg = wb.CreateStyle();
                styleMsg.Font.Size = 11;//文字大小
                styleMsg.IsTextWrapped = true;

                foreach (OutBoundProblemBean outBoundBean in arListStatics)
                {
                    curIndex++;

                    int colum = 0;
                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.wayBill);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.regSite);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.firstTypeName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.secondTypeName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.probleDescription);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue("派件");
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.registrationNetworkName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.createTime);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.customerCode);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.customerName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue("无需反馈");
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.replyStatus);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.replyContent);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    curRowIndex++;
                }
            }
            catch (Exception e)
            {
                logHelp.LogHelper.Instance.writeLog("GenExcelForProblemReturn 生成excel数据异常" + e.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }





        public bool GenExcelForOutBoundAckNew(ArrayList arListStatics, string sPath, out string outPutFileUrl)
        {
            outPutFileUrl = "";
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            try
            {
                sPath = sPath + "\\" + "出港自动回复汇总_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
                outPutFileUrl = sPath;
                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中
                style.IsTextWrapped = true;

                int rowIndex = 0;
                int culumnIndex = 0;
                sheet.Cells[rowIndex, culumnIndex].PutValue("单号");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 25);//设置宽度

                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("寄件网点");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 25);//设置宽度

                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("问题件一级类型");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 25);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("问题件二级类型");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 40);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("问题件描述");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 40);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("操作类型");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("登记网点");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 30);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("登记时间");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("客户编码");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 15);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("客户名称");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 40);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("反馈人员");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("回复是否成功");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("回复内容");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 60);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("组别");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("客服");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度
                culumnIndex++;

                sheet.Cells[rowIndex, culumnIndex].PutValue("群");
                sheet.Cells[rowIndex, culumnIndex].SetStyle(style);
                sheet.Cells.SetColumnWidth(culumnIndex, 10);//设置宽度
                culumnIndex++;


                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1;

                Aspose.Cells.Style styleMsg = wb.CreateStyle();
                styleMsg.Font.Size = 11;//文字大小
                styleMsg.IsTextWrapped = true;

                foreach (OutBoundProblemBean outBoundBean in arListStatics)
                {
                    curIndex++;

                    int colum = 0;
                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.wayBill);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;
                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.regSite);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;


                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.firstTypeName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.secondTypeName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.probleDescription);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue("派件");
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.registrationNetworkName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.createTime);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.customerCode);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.customerName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue("无需反馈");
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.replyStatus);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.replyContent);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.groupType);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.employee);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    sheet.Cells[curRowIndex, colum].PutValue(outBoundBean.groupName);
                    sheet.Cells[curRowIndex, colum].SetStyle(styleMsg);
                    colum++;

                    curRowIndex++;
                }
            }
            catch (Exception e)
            {
                logHelp.LogHelper.Instance.writeLog("GenExcelForProblemReturn 生成excel数据异常" + e.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }
            #endregion
            return res;
        }



        public bool GenExcelForStaticZc(ArrayList arListStatics, string sPath)
        {
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            int curR = 0;
            try
            {


                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中

                int rowIndex = 0;
                sheet.Cells[rowIndex, 0].PutValue("单号");
                sheet.Cells[rowIndex, 0].SetStyle(style);
                sheet.Cells.SetColumnWidth(0, 20);//设置宽度

                sheet.Cells[rowIndex, 1].PutValue("申报网点");
                sheet.Cells[rowIndex, 1].SetStyle(style);
                sheet.Cells.SetColumnWidth(1, 30);//设置宽度

                sheet.Cells[rowIndex, 2].PutValue("申报时间");
                sheet.Cells[rowIndex, 2].SetStyle(style);
                sheet.Cells.SetColumnWidth(2, 15);//设置宽度

                sheet.Cells[rowIndex, 3].PutValue("一级异常类型");
                sheet.Cells[rowIndex, 3].SetStyle(style);
                sheet.Cells.SetColumnWidth(3, 6);//设置宽度

                sheet.Cells[rowIndex, 4].PutValue("二级异常类型");
                sheet.Cells[rowIndex, 4].SetStyle(style);
                sheet.Cells.SetColumnWidth(4, 15);//设置宽度

                sheet.Cells[rowIndex, 5].PutValue("异常说明");
                sheet.Cells[rowIndex, 5].SetStyle(style);
                sheet.Cells.SetColumnWidth(5, 60);//设置宽度

                sheet.Cells[rowIndex, 6].PutValue("责任网点");
                sheet.Cells[rowIndex, 6].SetStyle(style);
                sheet.Cells.SetColumnWidth(6, 60);//设置宽度

                sheet.Cells[rowIndex, 7].PutValue("仲裁状态");
                sheet.Cells[rowIndex, 7].SetStyle(style);
                sheet.Cells.SetColumnWidth(7, 20);//设置宽度

                sheet.Cells[rowIndex, 8].PutValue("结案时间");
                sheet.Cells[rowIndex, 8].SetStyle(style);
                sheet.Cells.SetColumnWidth(8, 20);//设置宽度

                sheet.Cells[rowIndex, 9].PutValue("仲裁处理意见");
                sheet.Cells[rowIndex, 9].SetStyle(style);
                sheet.Cells.SetColumnWidth(9, 30);//设置宽度

                sheet.Cells[rowIndex, 10].PutValue("申诉处理意见");
                sheet.Cells[rowIndex, 10].SetStyle(style);
                sheet.Cells.SetColumnWidth(10, 60);//设置宽度





                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1; ;
                foreach (ZCInfo zcInfo in arListStatics)
                {
                    if (zcInfo == null)
                    {
                        continue;
                    }
                    curIndex++;
                    curR++;
                    try
                    {
                        sheet.Cells[curRowIndex, 0].PutValue(zcInfo.barcode);
                        sheet.Cells[curRowIndex, 1].PutValue(zcInfo.createNetworkName);
                        sheet.Cells[curRowIndex, 2].PutValue(zcInfo.createTime);
                        sheet.Cells[curRowIndex, 3].PutValue(zcInfo.firstType);
                        sheet.Cells[curRowIndex, 4].PutValue(zcInfo.secondType);
                        sheet.Cells[curRowIndex, 5].PutValue(zcInfo.exceptionDesc);

                        sheet.Cells[curRowIndex, 6].PutValue(zcInfo.responsibilityNetworkName);
                        sheet.Cells[curRowIndex, 7].PutValue(zcInfo.status);
                        sheet.Cells[curRowIndex, 8].PutValue(zcInfo.closingTime);
                        sheet.Cells[curRowIndex, 9].PutValue(zcInfo.arbitrationAdjudicateOpinion);
                        sheet.Cells[curRowIndex, 10].PutValue(zcInfo.appealAdjudicateOpinion);

                        curRowIndex++;
                    }
                    catch (Exception e)
                    {

                    }


                }
            }
            catch (Exception e)
            {

                logHelp.LogHelper.Instance.writeLog("GenExcelForStatic 生成excel数据异常 curR=" + curR.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }

            #endregion
            return res;
        }



        public bool GenExcelForStaticTerminal(ArrayList arListStatics, string sPath)
        {
            Workbook wb = new Workbook(FileFormatType.Xlsx);
            bool res = false;
            int curR = 0;
            try
            {


                Worksheet sheet = wb.Worksheets[0];
                sheet.Name = "汇总结果";
                // 为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center;  //设置居中
                style.Font.Size = 11;//文字大小
                style.Font.IsBold = true;//粗体
                style.HorizontalAlignment = TextAlignmentType.Center;//文字居中

                int rowIndex = 0;
                sheet.Cells[rowIndex, 0].PutValue("单号");
                sheet.Cells[rowIndex, 0].SetStyle(style);
                sheet.Cells.SetColumnWidth(0, 20);//设置宽度

                sheet.Cells[rowIndex, 1].PutValue("寄件网点");
                sheet.Cells[rowIndex, 1].SetStyle(style);
                sheet.Cells.SetColumnWidth(1, 30);//设置宽度

                sheet.Cells[rowIndex, 2].PutValue("派件网点");
                sheet.Cells[rowIndex, 2].SetStyle(style);
                sheet.Cells.SetColumnWidth(2, 30);//设置宽度

                sheet.Cells[rowIndex, 3].PutValue("延误天数");
                sheet.Cells[rowIndex, 3].SetStyle(style);
                sheet.Cells.SetColumnWidth(3, 15);//设置宽度

                sheet.Cells[rowIndex, 4].PutValue("入库时间");
                sheet.Cells[rowIndex, 4].SetStyle(style);
                sheet.Cells.SetColumnWidth(4, 25);//设置宽度

                sheet.Cells[rowIndex, 5].PutValue("出库时间");
                sheet.Cells[rowIndex, 5].SetStyle(style);
                sheet.Cells.SetColumnWidth(5, 25);//设置宽度

                sheet.Cells[rowIndex, 6].PutValue("签收时间");
                sheet.Cells[rowIndex, 6].SetStyle(style);
                sheet.Cells.SetColumnWidth(6, 25);//设置宽度






                //foreach(string strTaskType in header)
                int curRowIndex = 1;
                int curIndex = -1; ;
                foreach (TerminalStaticBean termianlBean in arListStatics)
                {
                    if (termianlBean == null)
                    {
                        continue;
                    }
                    curIndex++;
                    curR++;
                    try
                    {
                        sheet.Cells[curRowIndex, 0].PutValue(termianlBean.barcode);
                        sheet.Cells[curRowIndex, 1].PutValue(termianlBean.lanshouSite);
                        sheet.Cells[curRowIndex, 2].PutValue(termianlBean.dispatchSite);
                        sheet.Cells[curRowIndex, 3].PutValue(termianlBean.delayDays);
                        sheet.Cells[curRowIndex, 4].PutValue(termianlBean.inWareHouseTime);
                        sheet.Cells[curRowIndex, 5].PutValue(termianlBean.outWareHouseTime);

                        sheet.Cells[curRowIndex, 6].PutValue(termianlBean.signTime);


                        curRowIndex++;
                    }
                    catch (Exception e)
                    {

                    }


                }
            }
            catch (Exception e)
            {

                logHelp.LogHelper.Instance.writeLog("GenExcelForStatic 生成excel数据异常 curR=" + curR.ToString());
            }
            #region 输出到Excel
            using (MemoryStream ms = new MemoryStream())
            {
                wb.Save(sPath);
                wb = null;
                res = true;
            }

            #endregion
            return res;
        }


    }
}
